﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.Common;

namespace Runda.CMS.Dal
{
    public partial class CtrtCustomerDal
    {
        public DataTable GetCustomerTableByUserID(Guid UserID)
        {
            string sql = @"
with cte(DepartmentID,DepartmentName,ParentID)
as
(
select DepartmentID,DepartmentName,ParentID from Cms_Sys_Department where DepartmentID in (select DepartmentID from Cms_Sys_UserDepartment where UserID =@UserID)
union all
select Dept.DepartmentID,Dept.DepartmentName,Dept.ParentID from Cms_Sys_Department Dept JOIN Cte ON Dept.ParentID = Cte.DepartmentID
)


select * from Cms_Ctrt_Customer where CustomerID in 
(
	select CustomerID from Cms_Ctrt_CustomerUser where UserID in 
	(
		select UserID from Cms_Sys_UserDepartment where DepartmentID in
		(
			select DepartmentID from cte		
		)
	)
)
";

            DbCommand dbcommand = CurrentDatabase.GetSqlStringCommand(sql); 
            CurrentDatabase.AddInParameter(dbcommand, "@UserID", System.Data.DbType.Guid, UserID);

            return CurrentDatabase.ExecuteDataSet(dbcommand).Tables[0];
        }

        public void SynchroCustomer()
        {
            DbCommand dbcommand = CurrentDatabase.GetStoredProcCommand("sync_Customer");
            dbcommand.CommandTimeout = 30000000;

            CurrentDatabase.ExecuteNonQuery(dbcommand);
        }

        public DataTable GetCustomerList_export(string WhereClause, string OrderBy)
        {
            string sqlSb = @"
                select
                c.company           as [所属公司] 
	            ,c.CustomerCode		as [客户编号]
	            ,c.CustomerAbbName 	as [客户名称]
	            ,c.CustomerAddress	as [地址]
	            ,c.Contactor		as [联系人]
	            ,c.Contactphone		as [联系电话]
                ,c.SalesModelType   as [销售模式]
                ,c.SalesType        as [销售方式]
	            ,v.Age				as [账龄]
	            ,u.UserName			as [业务员]
            from Cms_Ctrt_Customer c
            left join Cms_Sys_User u on u.UserID = c.Salesman
            left join vw_CustomerAge v on v.CustomerID = c.CustomerID
                where 1=1 {0} order by {1} Asc";
            string sqlStr = string.Format(sqlSb.ToString(), WhereClause, OrderBy);

            DbCommand dbCommand = this._db.GetSqlStringCommand(sqlStr);

            DataSet ds = CurrentDatabase.ExecuteDataSet(dbCommand);
            return ds.Tables[0];
        }

        //客户
        public DataTable GetCustomerList(string WhereClause, string OrderBy, int PageSize, int PageIndex, out int RecordCount)
        {
            string sqlSb = @"select c.CustomerID
	,c.CustomerCode
	,case when c.CustomerAbbName is null then c.CustomerName else c.CustomerAbbName end as CustomerName
	,c.CustomerAddress
	,c.Contactor
	,c.Contactphone
	,v.Age
	,u.UserName 
    ,SalesModelType
    ,SalesType
    ,c.company
from Cms_Ctrt_Customer c
left join Cms_Sys_User u on u.UserID = c.Salesman
left join vw_CustomerAge v on v.CustomerID = c.CustomerID
where  1=1 {0} ";

            string sqlStr = string.Format(sqlSb.ToString(), WhereClause);

            string CmdText = @"WITH T AS
(
SELECT ROW_NUMBER() OVER(ORDER BY {2} ) AS row_number, * 
  FROM ({0}) as A
)
SELECT * from T WHERE row_number > @StartRowNum AND  row_number <= @EndRowNum
select count(*) from ({1}) as B 
";

            CmdText = string.Format(CmdText, sqlStr, sqlStr, OrderBy);

            DbCommand dbCommand = this._db.GetSqlStringCommand(CmdText);
            CurrentDatabase.AddInParameter(dbCommand, "@StartRowNum", System.Data.DbType.Int32, (PageIndex - 1) * PageSize);
            CurrentDatabase.AddInParameter(dbCommand, "@EndRowNum", System.Data.DbType.Int32, PageIndex * PageSize);

            DataSet ds = CurrentDatabase.ExecuteDataSet(dbCommand);
            RecordCount = Convert.ToInt32(ds.Tables[1].Rows[0][0]);
            return ds.Tables[0];
        }

        //客户服务（客户信息+服务仪器）
        public DataTable GetCustomerInstrumentList(string WhereClause, string OrderBy, int PageSize, int PageIndex, out int RecordCount)
        {
            string sqlSb = @"select *
from CustomerInstrument
where  1=1 {0} ";

            string sqlStr = string.Format(sqlSb.ToString(), WhereClause);

            string CmdText = @"WITH T AS
(
SELECT ROW_NUMBER() OVER(ORDER BY {2} ) AS row_number, * 
  FROM ({0}) as A
)
SELECT * from T WHERE row_number > @StartRowNum AND  row_number <= @EndRowNum
select count(*) from ({1}) as B 
";

            CmdText = string.Format(CmdText, sqlStr, sqlStr, OrderBy);

            DbCommand dbCommand = this._db.GetSqlStringCommand(CmdText);
            CurrentDatabase.AddInParameter(dbCommand, "@StartRowNum", System.Data.DbType.Int32, (PageIndex - 1) * PageSize);
            CurrentDatabase.AddInParameter(dbCommand, "@EndRowNum", System.Data.DbType.Int32, PageIndex * PageSize);

            DataSet ds = CurrentDatabase.ExecuteDataSet(dbCommand);
            RecordCount = Convert.ToInt32(ds.Tables[1].Rows[0][0]);
            return ds.Tables[0];
        }

        public DataTable ExportCustomerInstrumentList(string WhereClause, string OrderBy)
        {
            string sqlSb = @"select 
instrumentCode as [仪器编号]
,instrumentName as [仪器名称]
,ManufacInstrumentCode as [仪器厂家编号]
,CompanyInstrumentCode as [仪器公司编号]
,InstallDate as [安装日期]
,SerialNum as [序列号]
,InstrumentStatusName as [仪器状态]
,InstrumentBeginUse as [开始使用日期]
,SoftWareVersion as [软件版本]
,dy1 as [供电电压]
,dy2 as [接地电压]
 ,case when HasReadNum = 0 then '否' else '是' end as [是否存在读数]
 ,case when ReadNumType = 1 then '计数总数' when ReadNumType = 2 then '读数日平均数' end as [读数方式]
 ,CustomerName as [客户名称]
,CustomerArea as [区域]
,CustomerAddress as [地址]
,Sales as [销售员]
,InstrumentWhere as [仪器所在科室]
,WhereTel as [科室固定电话]
,CalibrationFrequency as [校准频率（月）]
	 ,CalibrationbeginDate as [校准开始日期]
	,AdjustMan as [校准负责人]
     ,GuaranteeFrequency as [保养频率（月）]
	 ,GuaranteebeginDate as [保养开始日期]
	,ConservationMan as [保养负责人]
     ,ReturnVisitFrequency as [回访频率（月）]
	 ,ReturnVisitbeginDate as [回访开始日期]
    ,ReturnVisitMan as [回访负责人]
    ,GuaranteeInfoName as [保修情况]
	 ,WarrantyFrom as [保修开始日期]
	 ,WarrantyTo as [保修截止日期]
	,DayEstimate as [日使用量预估]
     ,HasTrainCertificate as [是否发放培训证书]
	,HasAgency	as [是否存在代理商]
	,AgencyName	as [代理商名称]
	,AgencyMan	as [代理商人员]
	,AgencyTel	as [代理商电话]
	,Memo	as [备注]
from CustomerInstrument
where  1=1 {0} order by {1} ";

            string sqlStr = string.Format(sqlSb.ToString(), WhereClause,OrderBy);


            DbCommand dbCommand = this._db.GetSqlStringCommand(sqlStr);
           
            DataSet ds = CurrentDatabase.ExecuteDataSet(dbCommand);
            return ds.Tables[0];
        }

    }
}
